#-*- coding: utf8 -*-
import MySQLdb
import string
import xlsxwriter
import datetime

# 定义时间标志变量
sheet_time = datetime.datetime.now()
sheet_mark = sheet_time.strftime('%Y-%m-%d')
book_mark = sheet_time.strftime('%Y%m%d')

# 定义输出excel文件名
# workbook = xlsxwriter.Workbook('select_'+book_mark+'.xlsx')
workbook = xlsxwriter.Workbook('lou_dong_news.xlsx')

# 定义sheet的名字
worksheet = workbook.add_worksheet(sheet_mark)

# 定义sheet中title的字体format
bold = workbook.add_format({'bold': True})

# 定义sql查询命令
cmd="select * from greatwall.lou_dong_news;"

# 定义链接mysql的用户信息 字典
Loginfo = {'USER':'root', 'PSWD':'123456', 'HOST':'localhost', 'PORT':3306}

# 调用MySQLdb模块 链接 mysql
conn=MySQLdb.connect(host=Loginfo['HOST'],user=Loginfo['USER'],passwd=Loginfo['PSWD'],port=Loginfo['PORT'],charset='utf8')
cur=conn.cursor()
cur.execute(cmd)

# 查询数据结果和字段名字 赋值给两个变量
result = cur.fetchall()
fields = cur.description # get column name

# 将结果写入excel中

# 定义title的坐标：row=0，col=0~字段总数 也就是excel的第一行：0，0  ~ 0，len(fields)

# 关于fields的结果如下图：通过fields[field][0] 获取字段名


for field in range(0,len(fields)):
    # worksheet.write(0,field,fields[field][0],bold)
    # worksheet = wb.add_sheet(u'资产负债表')
    worksheet.write(0, 0, u'url')
    worksheet.write(0, 1, u'标题')
    worksheet.write(0, 2, u'CNVD-ID')
    worksheet.write(0, 3, u'发布时间')
    worksheet.write(0, 4, u'危害级别')
    worksheet.write(0, 5, u'影响产品')
    worksheet.write(0, 6, u'BUGTRAQ ID')
    worksheet.write(0, 7, u'CVE ID')
    worksheet.write(0, 8, u'漏洞描述')
    worksheet.write(0, 9, u'漏洞类型')
    worksheet.write(0, 10, u'URL')
    worksheet.write(0, 11, u'参考链接')
    worksheet.write(0, 12, u'漏洞解决方案')
    worksheet.write(0, 13, u'漏洞发现者')
    worksheet.write(0, 14, u'厂商补丁')
    worksheet.write(0, 15, u'验证信息')
    worksheet.write(0, 16, u'报送时间')
    worksheet.write(0, 17, u'收录时间')
    worksheet.write(0, 18, u'更新时间')
    worksheet.write(0, 19, u'漏洞附件')

#数据坐标0,0 ~ row,col   row取决于：result的行数；col取决于fields的总数
for row in range(1,len(result)+1):
    for col in range(0,len(fields)):
        worksheet.write(row,col,u'%s' % result[row-1][col])
cur.close()
conn.close()
workbook.close()